create database a
on
(
	name='a',
	filename='D:\temp\ATM.mdf',
	size=5,
	maxsize=10,
	filegrowth=15%
)
log on
(
	name='a_log',
	filename='D:\temp\ATM.ldf',
	size=5,
	maxsize=10,
	filegrowth=15%
)
go
use a
go

create table orders
(
--订单编号（orderId 主键）  订购日期（orderDate）
	orderId int primary key,
	orderDate datetime default(getdate())
)
--订购项目表（orderItem），列为：
--项目编号（ItemiD）订单编号（orderId）产品类别（itemType）
--产品名称（itemName） 订购数量（theNumber）  订购单价（theMoney）
create table orderItem
(
--订单编号（orderId 主键）  订购日期（orderDate）
	ItemiD int identity(1,1),
	orderId int,
	itemType varchar(20),
	itemName varchar(20),
	theNumber int, 
	theMoney int
)

select * from orders
select * from orderItem
insert into orderItem(orderId,itemType,itemName,theNumber,theMoney)
select 1,'文具','笔',72,2 union
select 1,'文具','尺',10,1 union
select 1,'体育用品','篮球',1,56 union
select 2,'文具','笔',36,2 union
select 2,'文具','固体胶',20,3 union
select 2,'日常用品','透明胶',2,1 union
select 2,'体育用品','羽毛球',20,3 union
select 3,'文具','订书机',20,3 union
select 3,'文具','订书针',10,3 union
select 3,'文具','栽纸刀',5,5 union
select 4,'文具','笔',20,2 union
select 4,'文具','信纸',50,1 union
select 4,'日常用品','毛巾',4,5 union
select 4,'日常用品','透明胶',30,1 union
select 4,'体育用品','羽毛球',20,3 

insert into orders(orderId,orderDate)
select 1,'2008-01-12' union
select 2,'2008-02-10' union
select 3,'2008-02-15' union
select 4,'2008-03-10' 
select * from orders
select * from orderItem

select SUM(theNumber) 数量总和 from orderItem

select orderId,SUM(theNumber),AVG(theMoney) from orderItem group by orderId 
having(AVG(theMoney)<10 and orderId <3)

select orderId,SUM(theNumber),AVG(theMoney) from orderItem group by orderId 
having(AVG(theMoney)<10 and SUM(theNumber)>50)

select itemType,count(theNumber) from orderItem group by itemType

select itemType,SUM(theNumber),AVG(theMoney) from orderItem group by itemType
having(SUM(theNumber)>100 and AVG(theMoney)>100)

select itemName 产品名称,count(orderId) 订购次数,SUM(theNumber) 总数,AVG(theMoney) 平均单价 from orderItem group by itemName